
import javax.swing.*;
import javax.swing.event.ListSelectionEvent;
import javax.swing.event.ListSelectionListener;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableModel;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.sql.*;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Vector;
import java.util.Date;

/**Graphic design for this page by Lanny Wilson
 * Code and parts of page layout by Fraser Hogg
 * 
 * 
* This code was edited or generated using CloudGarden's Jigloo
* SWT/Swing GUI Builder, which is free for non-commercial
* use. If Jigloo is being used commercially (ie, by a corporation,
* company or business for any purpose whatever) then you
* should purchase a license for each developer using Jigloo.
* Please visit www.cloudgarden.com for details.
* Use of Jigloo implies acceptance of these licensing terms.
* A COMMERCIAL LICENSE HAS NOT BEEN PURCHASED FOR
* THIS MACHINE, SO JIGLOO OR THIS CODE CANNOT BE USED
* LEGALLY FOR ANY CORPORATE OR COMMERCIAL PURPOSE.
*/
public class pkg extends javax.swing.JFrame {
	/**
	 * 
	 */
	private static final long serialVersionUID = 7121455980368227085L;
	private JLabel lblLogo;
	private JPanel block1;
	private JPanel divider2;
	private JButton btnIncnt;
	private JButton btnHome;
	private JPanel divider1;
	private JTable JTbPkg;
	private JScrollPane jScrollPane1;
	private JTextField txtName;
	private JButton btnReset;
	private JLabel lblPkgName;
	private JButton btnDel;
	private JButton btnUpdate;
	private JButton btnNew;
	private JTextField txtEDate;
	private JTextField txtSDate;
	private JScrollPane jScrollPane2;
	private JTextArea txtDesc;
	private JTextField txtCom;
	private JTextField txtPrice;
	private JLabel lblComm;
	private JLabel lblPrice;
	private JLabel lblDesc;
	private JLabel lblEndDate;
	private JLabel lblStDate;
	private JLabel lblName;
	private JButton btnAgent;
	private JLabel imgHome;
	private JButton btnLogout;

	//declare table data and headers
	private Vector<String> colNames = new Vector<String>();
	private Vector<Vector> dataRows = new Vector<Vector>();
	SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
	DecimalFormat decF = new DecimalFormat("0.00");

	private  Statement getCon() throws ClassNotFoundException, SQLException
	{
		Class.forName("com.mysql.jdbc.Driver");
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/travelexperts", "root","root");
		if (!conn.isClosed())
			System.out.println("Successfully connected.");
		return conn.createStatement();
	}
	//gets headers for jtable from metadata
	public void loadColData() throws SQLException, ClassNotFoundException
	{

		Statement stmt = getCon();
		ResultSet rs = stmt.executeQuery("SELECT * FROM Packages");
		ResultSetMetaData rsmd = rs.getMetaData();
	
		for (int i = 1; i <= rsmd.getColumnCount(); i++)
		{
			colNames.add(rsmd.getColumnName(i));
		}
		stmt.close();
	}
	//loads the row data to jtable
	public void loadRowData() 
	{
		try {
			ResultSet rs;
			Statement stmt = getCon();
			rs = stmt.executeQuery("SELECT * FROM Packages");
			
			while (rs.next())
			{
				//get value, format and add as string to vector for table data
				Vector<String> row = new Vector<String>(); 
				int PkgId = rs.getInt(1);
				row.add(String.valueOf(PkgId));
				String PkgName = rs.getString(2);
				row.add(PkgName);
				Date PkgStart = rs.getDate(3);
				row.add(df.format(PkgStart));
				Date PkgEnd = rs.getDate(4);
				row.add(df.format(PkgEnd));
				String PkgDesc = rs.getString(5);
				row.add(PkgDesc);
				double PkgBase = rs.getDouble(6);
				row.add(decF.format(PkgBase));
				double PkgCom = rs.getDouble(7);
				row.add(decF.format(PkgCom));
				dataRows.add(row);
				//pkg.JTbPkgModel.
			}
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
	}
	// validates data for SQL statement building
	public boolean pkgValidation(){
		String msg ="";
		boolean error = false;
		if (1 > txtName.getText().length() || txtName.getText().length() > 51){
			msg += "Package name must be 1 to 50 Characters in length.\n";
			error = true;
		}
		String cond = null;
		try{
			df.parse(txtSDate.getText());
			df.parse(txtEDate.getText());
		}
		catch(ParseException e){
			cond = ""+e;
		}
		if (cond!=null){
			msg += "Dates must be YYYY-MM-DD. \n";
			error = true;
		}
		cond = null;
		double p = 1;
		double c = 0;
		try{
			p = Double.parseDouble(txtPrice.getText());
			c = Double.parseDouble(txtCom.getText());
		} catch (NumberFormatException e){
			cond = ""+e;
		}
		if (cond!=null){
			msg += "Price and Commission must be numerical. \n";
			error = true;
		}
		if (p<c){
			msg += "Commission can not be greater than Price. \n";
			error = true;
		}
		if (1 > txtDesc.getText().length() || txtDesc.getText().length() > 51){
			msg += "Package Desc must be 1 to 50 Characters in length. \n";
			error = true;
		}
		if(error){
			JOptionPane.showMessageDialog(null, msg);
		}
		return error;
	}
	public boolean PkgUpdate( int PkgId, String PkgName, Date PkgStartDate, Date PkgEndDate, String PkgDesc,
				double PkgBasePrice, double PkgAgencyCommission) throws ClassNotFoundException, SQLException, NumberFormatException
	{
		Statement stmt = getCon();
		int numRows =0;
		String sql="";
		try{
		sql = "UPDATE Packages SET PkgName = '" + PkgName + "', PkgStartDate = '" + 
				df.format(PkgStartDate) + "', PkgEndDate = '" + df.format(PkgEndDate) + "', PkgDesc = '" +
				PkgDesc + "', PkgBasePrice = '"+PkgBasePrice+"', PkgAgencyCommission = '" + PkgAgencyCommission + "' WHERE PackageId = " + PkgId;
		numRows = stmt.executeUpdate(sql);
		}
		catch(SQLException Ex){
			System.out.println(sql + Ex);
		}
		finally{
		stmt.close();
        if (numRows == 0)
        {
        	return false;
        }
        else
        {
        	return true;
        }
		}
	}
	
	public boolean PkgAdd(String PkgName, Date PkgStartDate, Date PkgEndDate, String PkgDesc,
			double PkgBasePrice, double PkgAgencyCommission) throws ClassNotFoundException, SQLException, NumberFormatException
	{
		Statement stmt = getCon();
		int numRows =0;
		String sql="";
		try{
			sql = "INSERT INTO Packages (PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission) values"
					+ "('" + PkgName + "','" + df.format(PkgStartDate) + "', '" + df.format(PkgEndDate) + "', '" + PkgDesc + "', '"
					+ PkgBasePrice + "', '"+ PkgAgencyCommission+"')";
			numRows = stmt.executeUpdate(sql);
		}
		catch(SQLException Ex){
			System.out.println(sql + Ex);
		}
		finally {
			stmt.close();
			if (numRows == 0) {
				return false;
			}
			else {
				return true;
			}
		}
	}
	
	public boolean PkgDelete(int PkgId) throws ClassNotFoundException, SQLException, NumberFormatException
	{
		Statement stmt = getCon();
		int numRows = 0;
		try {
			String sql = "DELETE FROM Packages WHERE PackageId = " + PkgId;
			numRows = stmt.executeUpdate(sql);
			stmt.close();
			if (numRows == 0)
	        {
	        	return false;
	        }
	        else
	        {
	        	return true;
	        }
		} catch (Exception e) {
			System.out.println("Delete table error!");
			return false;
		}
	}
	//clears text fields
	private void cleartext()
	{
		txtName.setText("");
		txtSDate.setText("");
		txtDesc.setText("");
		txtEDate.setText("");
		txtCom.setText("");
		txtPrice.setText("");
	}
	
	/**
	* Auto-generated main method to display this JFrame
	*/
	public static void main(String[] args) {
		SwingUtilities.invokeLater(new Runnable() {
			public void run() {
				pkg inst = new pkg();
				inst.setLocationRelativeTo(null);
				inst.setVisible(true);
			}
		});
	}
	
	public pkg() {
		super();
		setResizable(false);
		initGUI();
	}
	
	private void initGUI() {
		try {
			setDefaultCloseOperation(WindowConstants.DISPOSE_ON_CLOSE);
			getContentPane().setLayout(null);
			getContentPane().setBackground(new java.awt.Color(255,255,255));
			this.setTitle("Travel Experts Packages");
			{
				lblLogo = new JLabel();
				getContentPane().add(lblLogo);
				lblLogo.setBounds(24, 12, 257, 78);
				lblLogo.setBackground(new java.awt.Color(255,0,0));
				lblLogo.setIcon(new ImageIcon(getClass().getClassLoader().getResource("images/TravelLogo.jpg")));
			}
			{
				lblName = new JLabel();
				getContentPane().add(lblName);
				lblName.setText("Name");
				lblName.setBounds(18, 112, 77, 15);
				lblName.setFont(new java.awt.Font("SansSerif",1,12));
				lblName.setHorizontalAlignment(SwingConstants.RIGHT);
			}
			{
				txtName = new JTextField();
				getContentPane().add(txtName);
				txtName.setBounds(104, 109, 192, 20);
				txtName.setFont(new java.awt.Font("SansSerif",0,12));
				txtName.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
			}
			{
				lblStDate = new JLabel();
				getContentPane().add(lblStDate);
				lblStDate.setText("Start Date");
				lblStDate.setBounds(18, 136, 80, 13);
				lblStDate.setFont(new java.awt.Font("SansSerif",1,12));
				lblStDate.setHorizontalAlignment(SwingConstants.RIGHT);
			}
			{
				txtSDate = new JTextField();
				getContentPane().add(txtSDate);
				txtSDate.setBounds(104, 133, 125, 20);
				txtSDate.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
				txtSDate.setFont(new java.awt.Font("SansSerif",0,12));
			}
			{
				lblEndDate = new JLabel();
				getContentPane().add(lblEndDate);
				lblEndDate.setText("End Date");
				lblEndDate.setBounds(18, 158, 80, 14);
				lblEndDate.setFont(new java.awt.Font("SansSerif",1,12));
				lblEndDate.setHorizontalAlignment(SwingConstants.RIGHT);
			}
			{
				txtEDate = new JTextField();
				getContentPane().add(txtEDate);
				txtEDate.setBounds(104, 157, 125, 20);
				txtEDate.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
			}
			{
				lblPrice = new JLabel();
				getContentPane().add(lblPrice);
				lblPrice.setText("Base Price");
				lblPrice.setBounds(18, 182, 80, 17);
				lblPrice.setFont(new java.awt.Font("SansSerif",1,12));
				lblPrice.setHorizontalAlignment(SwingConstants.RIGHT);
			}
			{
				txtPrice = new JTextField();
				getContentPane().add(txtPrice);
				txtPrice.setBounds(104, 180, 73, 20);
				txtPrice.setFont(new java.awt.Font("SansSerif",0,12));
				txtPrice.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
			}
			{
				lblComm = new JLabel();
				getContentPane().add(lblComm);
				lblComm.setText("Commission");
				lblComm.setBounds(-6, 203, 104, 18);
				lblComm.setFont(new java.awt.Font("SansSerif",1,12));
				lblComm.setHorizontalAlignment(SwingConstants.RIGHT);
			}
			{
				txtCom = new JTextField();
				getContentPane().add(txtCom);
				txtCom.setBounds(104, 203, 73, 20);
				txtCom.setFont(new java.awt.Font("SansSerif",0,12));
				txtCom.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
			}
			{
				lblDesc = new JLabel();
				getContentPane().add(lblDesc);
				lblDesc.setText("Description");
				lblDesc.setBounds(5, 229, 93, 17);
				lblDesc.setFont(new java.awt.Font("SansSerif",1,12));
				lblDesc.setHorizontalAlignment(SwingConstants.RIGHT);
			}
			{
				btnNew = new JButton();
				btnNew.addMouseListener(new MouseAdapter() {
					@Override
					public void mouseClicked(MouseEvent arg0) {
						System.out.println("btnUpdate.actionPerformed");
						if (!pkgValidation())//validation function for later
						{
							//update database
							boolean ret = false;
							try {
								ret = PkgAdd(txtName.getText(),df.parse(txtSDate.getText()), df.parse(txtEDate.getText()), 
										txtDesc.getText(), Double.parseDouble(txtPrice.getText()), Double.parseDouble(txtCom.getText()));
								if (ret)
								{
									//change table data
									dataRows.clear();
									loadRowData();
									JTbPkg.repaint();
									cleartext();
									JOptionPane.showMessageDialog(null, "Addition successful.");
									btnUpdate.setEnabled(false);				        	
								}
								else
								{
									JOptionPane.showMessageDialog(null, "Addition failed");
								}
							} catch (ClassNotFoundException ex) {
								ex.printStackTrace();
							} catch (SQLException ex) {
								ex.printStackTrace();
							} catch (NumberFormatException e1) {
								// TODO Auto-generated catch block
								e1.printStackTrace();
							} catch (ParseException e1) {
								// TODO Auto-generated catch block
								e1.printStackTrace();
							}
						}
					}
				});
				getContentPane().add(btnNew);
				btnNew.setText("Add");
				btnNew.setBounds(316, 115, 82, 22);
				btnNew.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
				btnNew.setFont(new java.awt.Font("SansSerif",1,12));
			}
			{
				btnUpdate = new JButton();
				btnUpdate.addMouseListener(new MouseAdapter() {
					@Override
					public void mouseClicked(MouseEvent e) {
						System.out.println("btnUpdate.actionPerformed");
							if(!pkgValidation()){
								//update database
								boolean ret = false;
								try {
									int PId = Integer.parseInt((String) JTbPkg.getValueAt(JTbPkg.getSelectedRow(),0));
									ret = PkgUpdate(PId, txtName.getText(),df.parse(txtSDate.getText()), df.parse(txtEDate.getText()), 
											txtDesc.getText(), Double.parseDouble(txtPrice.getText()), Double.parseDouble(txtCom.getText()));
									if (ret)
									{
										//change table data
										dataRows.clear();
										loadRowData();
										JTbPkg.repaint();
										JOptionPane.showMessageDialog(null, "Update successful");
										btnUpdate.setEnabled(false);				        	
									}
									else
									{
										JOptionPane.showMessageDialog(null, "update failed");
									}
								} catch (ClassNotFoundException ex) {
									ex.printStackTrace();
								} catch (SQLException ex) {
									ex.printStackTrace();
								} catch (NumberFormatException e1) {
									// TODO Auto-generated catch block
									e1.printStackTrace();
								} catch (ParseException e1) {
									// TODO Auto-generated catch block
									e1.printStackTrace();
								}
							}
					}
				});
				getContentPane().add(btnUpdate);
				btnUpdate.setText("Update");
				btnUpdate.setBounds(316, 146, 82, 22);
				btnUpdate.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
				btnUpdate.setFont(new java.awt.Font("SansSerif",1,12));
			}
			{
				btnDel = new JButton();
				btnDel.addMouseListener(new MouseAdapter() {
					@Override
					public void mouseClicked(MouseEvent e) {
						System.out.println("btnDelete.actionPerformed");
						
						if (JTbPkg.getSelectedRow() == -1)
						{
							JOptionPane.showMessageDialog(null, "You must select a row first");
						}
						else
						{
						try {
							if (PkgDelete(Integer.parseInt((String) JTbPkg.getValueAt(JTbPkg.getSelectedRow(), 0))))
							{
								dataRows.clear();
								loadRowData();
								JTbPkg.repaint();
								cleartext();
								JOptionPane.showMessageDialog(null, "deleted successfully");
								btnNew.setEnabled(true);
								btnDel.setEnabled(false);
								btnUpdate.setEnabled(false);
							}
							else
							{
								JOptionPane.showMessageDialog(null, "delete failed");
							}
						} 
						catch (ClassNotFoundException ex) {
							ex.printStackTrace();
						} 
						catch (SQLException ex) {
							// TODO Auto-generated catch block
							ex.printStackTrace();
						}
						}
					}
				});
				getContentPane().add(btnDel);
				btnDel.setText("Delete");
				btnDel.setBounds(316, 210, 82, 22);
				btnDel.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
				btnDel.setFont(new java.awt.Font("SansSerif",1,12));
			}
			{
				btnReset = new JButton();
				btnReset.addMouseListener(new MouseAdapter() {
					@Override
					public void mouseClicked(MouseEvent e) {
						System.out.println("btnReset.actionPerformed, event");
						//TODO add your code for btnReset.actionPerformed
						btnNew.setEnabled(true);
						btnUpdate.setEnabled(false);
						btnDel.setEnabled(false);
						cleartext();
					}
				});
				getContentPane().add(btnReset);
				btnReset.setText("Reset");
				btnReset.setBounds(316, 178, 82, 22);
				btnReset.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
				btnReset.setFont(new java.awt.Font("SansSerif",1,12));
			}
			{
				jScrollPane2 = new JScrollPane();
				getContentPane().add(jScrollPane2);
				jScrollPane2.setBounds(11, 249, 402, 66);
				{
					txtDesc = new JTextArea();
					jScrollPane2.setViewportView(txtDesc);
					txtDesc.setBounds(11, 249, 402, 66);
					txtDesc.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
					txtDesc.setFont(new java.awt.Font("SansSerif",0,12));
				}
			}
			{
				divider1 = new JPanel();
				getContentPane().add(divider1);
				divider1.setBounds(425, 26, 5, 300);
				divider1.setBackground(new java.awt.Color(0,0,0));
			}
			{
				divider2 = new JPanel();
				getContentPane().add(divider2);
				divider2.setBounds(690, 26, 5, 300);
				divider2.setBackground(new java.awt.Color(0,0,0));
			}
			{
				btnAgent = new JButton();
				getContentPane().add(btnAgent);
				btnAgent.setIcon(new ImageIcon(getClass().getClassLoader().getResource("images/btnAgent.jpg")));
				btnAgent.setBounds(526, 26, 70, 300);
				btnAgent.setBackground(new java.awt.Color(255,255,255));
				btnAgent.addMouseListener(new MouseAdapter() {
					public void mouseClicked(MouseEvent evt) {
						System.out.println("btnAgent.mouseClicked, event="+evt);
						//TODO add your code for btnAgent.mouseClicked
						agent inst = new agent();
						inst.setLocationRelativeTo(null);
						inst.setVisible(true);
						pkg.this.dispose();
					}
				});
			}
			{
				btnHome = new JButton();
				getContentPane().add(btnHome);
				btnHome.setIcon(new ImageIcon(getClass().getClassLoader().getResource("images/btnHm.jpg")));
				btnHome.setBounds(444, 26, 70, 300);
				btnHome.addMouseListener(new MouseAdapter() {
					public void mouseClicked(MouseEvent evt) {
						System.out.println("btnHome.mouseClicked, event="+evt);
						//TODO add your code for btnHome.mouseClicked
						home inst = new home();
						inst.setLocationRelativeTo(null);
						inst.setVisible(true);
						pkg.this.dispose();
					}
				});
			}
			{
				btnIncnt = new JButton();
				getContentPane().add(btnIncnt);
				btnIncnt.setIcon(new ImageIcon(getClass().getClassLoader().getResource("images/BtnProd.jpg")));
				btnIncnt.setBounds(608, 26, 70, 300);
				btnIncnt.addMouseListener(new MouseAdapter() {
					public void mouseClicked(MouseEvent evt) {
						System.out.println("btnIncnt.mouseClicked, event="+evt);
						PkgProd inst = new PkgProd();
						inst.setLocationRelativeTo(null);
						inst.setVisible(true);
						pkg.this.dispose();
					}
				});
			}
			{
				block1 = new JPanel();
				getContentPane().add(block1);
				block1.setBackground(new java.awt.Color(73,84,163));
				block1.setBounds(695, 101, 184, 225);
			}
			{
				imgHome = new JLabel();
				getContentPane().add(imgHome);
				imgHome.setBounds(0, 101, 425, 225);
				imgHome.setIcon(new ImageIcon(getClass().getClassLoader().getResource("images/pkgBk.jpg")));
			}
			{
				loadColData();
				loadRowData();
				jScrollPane1 = new JScrollPane();
				getContentPane().add(jScrollPane1);
				jScrollPane1.setBounds(12, 338, 855, 140);
				jScrollPane1.setOpaque(true);
				{
					TableModel JTbPkgModel = 
							new DefaultTableModel(dataRows,colNames);
					JTbPkg = new JTable();
					
					JTbPkg.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
					jScrollPane1.setViewportView(JTbPkg);
					jScrollPane1.setOpaque(true);
					jScrollPane1.setBackground(new java.awt.Color(255,255,255));
					jScrollPane1.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
					JTbPkg.setModel(JTbPkgModel);
					JTbPkg.setBounds(6, 338, 754, 140);
					JTbPkg.setOpaque(true);
					JTbPkg.setFont(new java.awt.Font("SansSerif",0,10));
					JTbPkg.getSelectionModel().addListSelectionListener(new ListSelectionListener() {
						
						public void valueChanged(ListSelectionEvent e) {
							//Populate form with record
							txtCom.setText((String)JTbPkg.getValueAt(JTbPkg.getSelectedRow(), 6));
							txtDesc.setText((String)JTbPkg.getValueAt(JTbPkg.getSelectedRow(), 4));
							txtEDate.setText((String)JTbPkg.getValueAt(JTbPkg.getSelectedRow(), 3));
							txtName.setText((String)JTbPkg.getValueAt(JTbPkg.getSelectedRow(), 1));
							txtPrice.setText((String)JTbPkg.getValueAt(JTbPkg.getSelectedRow(), 5));
							txtSDate.setText((String)JTbPkg.getValueAt(JTbPkg.getSelectedRow(), 2));
														
							btnNew.setEnabled(true);
							btnDel.setEnabled(true);
							btnUpdate.setEnabled(true);
						}
					});
				}
			}
			{
				btnLogout = new JButton();
				getContentPane().add(btnLogout);
				btnLogout.setText("Logout");
				btnLogout.setBorderPainted(false);
				btnLogout.setFont(new java.awt.Font("SansSerif",0,10));
				btnLogout.setOpaque(false);
				btnLogout.setBounds(799, 4, 69, 20);
				btnLogout.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
				btnLogout.addMouseListener(new MouseAdapter() {
					public void mouseClicked(MouseEvent evt) {
						System.out.println("btnHome.mouseClicked, event="+evt);
						//TODO add your code for btnHome.mouseClicked
						home inst = new home();
						inst.setLocationRelativeTo(null);
						inst.setVisible(true);
						pkg.this.dispose();
					}
				});
			}
			{
				lblPkgName = new JLabel();
				getContentPane().add(lblPkgName);
				lblPkgName.setText("PACKAGES");
				lblPkgName.setBounds(707, 70, 160, 25);
				lblPkgName.setFont(new java.awt.Font("SansSerif",1,24));
				lblPkgName.setHorizontalAlignment(SwingConstants.CENTER);
			}
			pack();
			this.setSize(890, 519);
		} catch (Exception e) {
		    //add your error handling code here
			e.printStackTrace();
		}
	}

}
